# -*- coding:utf-8 -*-#
# -------------------------------------------------------------------------
# ProjectName:       zhcx-service-test
# FileName:          common_methods.py
# Author:            xun
# Date:              2024/7/16 9:13
# Description:
# --------------------------------------------------------------------------
'''
    写入读库、请求接口
'''
from requests.sessions import Session
from openpyxl import Workbook #后续可能要将数据展示给业务,作为备选
from set_log import SetLogger
from datetime import date,timedelta,datetime
import pyodbc
import json
class DuSql:
    '''
    目前先做sqlserver,后续加入mysql
    '''

    def __init__(self):
        self.server = 'erpdb-readonly.zhcxkj.com'  # sql server
        self.host = 'erpdb-readonly.zhcxkj.com'  # mysql
        self.user = 'irobotboxuser'
        self.password = 'HskwPanda047RAHRAxqktlref'
        self.database = 'irobotbox'
        self.port = 1433
        # self.table=PrettyTable()
        self.charset = 'ISO-8859-1'
        self.connect = pyodbc.connect((f'DRIVER={{SQL Server}};SERVER={self.server},{self.port};DATABASE={self.database};UID={self.user};PWD={self.password}'))
        self.cousor = self.connect.cursor()

    def get_all_youpinOrderSourceId(self):
        sql = "select DISTINCT OrderSourceID from RB_OrderSource_SaleAdmin rossa WHERE CustomerID = 1 and AdminID in (select ID from RB_Admin ra WHERE UserFullPath  LIKE \'{0}_{500}%\' and IsActive = 1 and IsLock = 0)"
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_all_advertising_fee(self,check_data):
        '''传入活动ID获取各个活动的广告费'''
        sql = "select RelatedID,SUM(AdCost)  from RB_Amazon_Advertisement_Product_Fees raapf where CustomerID = 1 and ReportDate = \'{0}\' and AdFeeType =2 group by RelatedID".format(check_data)
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_all_asin_fee(self,check_data):
        sql = "select RelatedID,Asin,SUM(AdCost)  from RB_Amazon_Advertisement_Product_Fees raapf where CustomerID = 1 and ReportDate = \'{0}\' and AdFeeType =2 group by RelatedID,Asin".format(
            check_data)
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_OrderNumber(self):
        '查询上月所有Asin+国家的出单量'
        sql = "SELECT Country,Asin,SUM(Orders)  from RB_Amazon_Advertisement_Share_OrderNumber raason WHERE CustomerID = 1 and ReportMonth = \'"+defult_date()[1]+"\' group by Country,Asin"
        self.cousor.execute(sql)
        return self.cousor.fetchall()

class sent_request:

    def __init__(self):
        self.s=Session()
        self.headers = {
            "Content_Type": "application/json",
            "Authorization": "Basic aXJvYm90Ym94OmkxcjVvOEJvdGJveA=="
        }

    def get_advertising_fee(self,OrderSourceId:list,check_date):
        '''先获取每个来源渠道的广告费,格式：[OrderSourceId1:{CampaignId1:Cost1},OrderSourceId2:{CampaignId2:Cost2}]
        查找日期默认为七天前的数据'''
        check_date = check_date.replace('-','')
        url = r'http://888cpcp-api.zhcxkj.com/api/SbCampaigns/SbCampaign/GetSbCampaignCost'
        data1 = {
                    "language": "zh_CN",
                    "current": 1,
                    "pageSize": 10000,
                    "orderBy": {
                        "field": "Id",
                        "order": 1
                    },
                    "input": {
                        "CustomerId": 1,
                        "OrderSourceIds": OrderSourceId,
                        "ReportStartDate": check_date,
                        "ReportEndDate": check_date
                    }
                }
        requst_result = self.s.post(url=url,json=data1,headers=self.headers).json()
        return requst_result['data']['list']

    def get_Campaign_Asin(self,CampaignId):
        url = r'http://888cpcp-api.zhcxkj.com/api/SbCampaigns/SbCampaign/GetSbCampaignAsinData'
        data1 = {
                    "language":"zh_CN",
                    "input":
                        {
                            "CustomerID": 1,
                            "CampaignId":CampaignId
                        }
                }
        request_result = self.s.post(url=url,json=data1,headers=self.headers).json()
        return request_result['data']['list'][0]

    def send_dingding_message(self,message,webhook_url=r"https://oapi.dingtalk.com/robot/send?access_token=81aabd44e5c1b493fde67801a60dcd8a6735f378819cc1de0e878d436f626789"):
        headers = {
            'Content-Type': 'application/json',
            'Charset': 'UTF-8'
        }
        data = {
            "msgtype": "text",
            "text": {
                "content": message
            },
            "at": {
                "atMobiles": ["18681557567", "18578798740"]
            }
        }
        response = self.s.post(url=webhook_url, headers=headers, data=json.dumps(data))
        if response.status_code == 200:
            print("Message sent successfully")
        else:
            print(f"Failed to send message, error code: {response.status_code}")

class create_log:

    def __init__(self):
        self.wb=Workbook()
        self.log=SetLogger()

    def create_excel(self,content:list,title:list=None):
        sheet=self.wb.active
        sheet.title='sheet1'
        # sheet.append(title)
        if content:
            sheet.append(content)
        self.wb.save('./save_log/%s.xlsx' % date.today())

    def create_txt(self,content):
        self.log.logger.info(f"日志打印》》》》{content}")

class more_tool:
    '''多线程(源数据接口性能太差,目前只能加在查库上)、加个定时脚本'''
    def datas_conversion(self,data,ifdict=None):
        '''将[('XXX', Decimal('-11')),('XXX', Decimal('-11'))] 转换成 [('XXX', -11)),('XXX', -11')]'''
        data1 = []
        for i in data:
            tuple1 = []
            tuple1.append(i[0])
            if i[1] is not None: #先排除event表sum(CurrencyAmount)为零的数据
                tuple1.append(float(i[1]))
            data1.append(tuple(tuple1))
        if ifdict:
            dict1={}
            for i in data1:
                dict1[i[0]]=i[1]
            data1=dict1
        return data1

    def data_conversion(self,data):
        '''将[(14679,), (16199,), (12263,)] 转换成 [14679,16199,12263]'''
        data1 = []
        for i in data:
            data1.append(i[0])
        return data1

    def data_conversion_dict(self,data):
        '''将[('XXX', Decimal('-11')),('XXX', Decimal('-11'))] 转换成 {xxx:-11,xxx:-22}'''
        dict1 = {}
        for i in data:
            dict1[i[0]]=round(float(i[1]),2)
        return dict1

    def datas_conversion_dict(self, data):
        '''将[('XXX',xxx, Decimal('-11')),('XXX',xxx,Decimal('-11'))] 转换成 {XXX:{xxx:-11,xxx:-22}}'''
        dict2 = {}
        for i in data:
            dict1 = {}
            if i[0] in dict2.keys():
               dict2[i[0]][i[1]] = float(i[2])
            else:
                dict1[i[1]] = float(i[2])
                dict2[i[0]] = dict1
        return dict2


def defult_date():
    '''默认查七天前的日期,以及七天前的当前月份'''
    now = datetime.now()
    seven_days_ago = now - timedelta(days=7)
    current_month = seven_days_ago.strftime('%Y-%m')
    return seven_days_ago.strftime('%Y-%m-%d'),current_month

if __name__ == '__main__':
    dm = DuSql()
    mt = more_tool()
    s = sent_request()
    log = create_log()
    # print(mt.data_conversion(dm.get_all_OrderSourceId()))
    # print(dm.get_all_youpinOrderSourceId())
    # list1 = s.get_advertising_fee(mt.data_conversion(dm.get_all_youpinOrderSourceId()),defult_date()[0])
    # dict1 = {}
    # for i in list1:
    #     list2 = []
    #     list2.append(i['OrderSourceCountry'])
    #     list2.append(i['Cost'])
    #     dict1[i["CampaignId"]]=list2
    # print(dict1)
    # data1 = s.get_Campaign_Asin('128403893999151')
    # print(data1['CampaignId'],data1['Asins'])
    # str1=mt.datas_conversion_dict(dm.get_all_asin_fee(defult_date()[0]))
    # log.create_txt(str1)